var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'realtimedb',
  password : '123456',
  database : 'realtimedb'
});
var tablename = "records";
connection.connect();

var createdb = "CREATE DATABASE IF NOT EXISTS realtimedb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
var createtable = "CREATE TABLE IF NOT EXISTS `records`( `id` INT UNSIGNED AUTO_INCREMENT,  `tag` VARCHAR(255) NOT NULL,   `value` VARCHAR(255) NOT NULL,   `secret` VARCHAR(32) NOT NULL,   PRIMARY KEY ( `id` )) ENGINE=InnoDB DEFAULT CHARSET=utf8;"

connection.query(createdb, (err,result) => {
	if(err){
			 //console.log("error create db");
			 return;
		}  
	connection.query("USE realtimedb", (err1,result1) => {
		if(err1){
				 //console.log("error select db" + err1);
				 return;
			}  
		});
		connection.query(createtable, (err2,result2) => {
		if(err2){
				 //console.log("error create table" + err2);
				 return;
			}  
		});
});

var md5 =require("md5");

var express = require('express');
var app = express();
const http = require('http').Server(app);
const io = require('socket.io')(http);
const port = process.env.PORT || 5050;

app.use(express.json());
app.use(express.urlencoded({extended:true}));
app.set('case sensitive routing', true);

app.get('/*', (req, res) => {
  res.sendFile(__dirname + '/index.html');
});

app.post('/*',(req,res)=>{
	
	if(req.path.endsWith("/storeavalue")){
		var tag = req.body.tag;
		var value = req.body.value;
		//if(value.startsWith("\"") && value.endsWith("\"")){value = value.substring(1, value.length-1)}
		var secret = req.body.secret;
		if(secret === undefined){
			secret = req.path.replace("/storeavalue","").replace("/","")
		}

		var modSql = 'UPDATE records SET value = ? WHERE BINARY tag = ? AND secret = ?';
		var modSqlParams = [value, tag, md5(secret)];
		connection.query(modSql,modSqlParams,function (err, result) {
			if(err){
				 //res.send(["ERROR",tag, err.message]);
				 ////console.log(err);
				 return;
			}  
			////console.log('UPDATE ID:',result.affectedRows); 
			if(result.affectedRows===0){
				var  addSql = 'INSERT INTO '+ tablename +'(tag,value,secret) VALUES(?,?,?)';
				var  addSqlParams = [tag, value,md5(secret)];
				connection.query(addSql,addSqlParams,function (err1, result1) {
					if(err1){
					//res.send(["ERROR",tag, err1.message]);
					//console.log(err1);
					 return;
					}        
			   
				});    
			}
			res.send(["STORED", tag, value]);
			var data ={};
			data.tag = tag;
			data.value = value;
			data.eventname = "DataChanged"
			io.of("/"+secret).emit("DataChanged", data);
		   
		   
		   //console.log('storeavalue'); 
		})
	}else if (req.path.endsWith("/getvalue")){
		var tag = req.body.tag;
		var secret = req.body.secret;
		if(secret === undefined){
			secret = req.path.replace("/getvalue","").replace("/","")
		}
		
		var  sql = 'SELECT * FROM '+ tablename +' WHERE BINARY  tag= ? and secret = ?';
		var selSqlParams = [tag, md5(secret)];
		
		connection.query(sql, selSqlParams, function (err, result) {
			if(err){
			  res.send(["ERROR",tag, err.message]);
			  return;
			}
			if(result.length===0){
				res.send(["VALUE",tag, "NotFound"]);
			}else{
				res.send(["VALUE",tag, result[0].value]);
			}
			//console.log("getvalue");
				
		});
	}
});

io.of(/^\/.+$/).on('connection', (socket) => {
  //console.log("new connect " + socket.nsp.name);
  socket.on('disconnect',(reason)=>{
	  //console.log("disconnect: " + socket.nsp.name);
  })
  
});

http.listen(port, () => {
  console.log(`realtimeDB server running at http://localhost:${port}/`);
});
